# -*- encoding: utf-8 -*-
#import odoo.addons.decimal_precision as dp
from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError, RedirectWarning, ValidationError
from odoo.tools.float_utils import float_round, float_compare
from collections import defaultdict
import xlwt


class account_cash_dialy_flows_report(models.Model):
    _name = 'account.cash.dialy.flows.report'
    _description = u'日记账现金流表'
    _order = 'period_id'

    name = fields.Char(u'报表名称', required=True, default='现金流表')
    period_id = fields.Many2one('account.period', u'期别')
    date_stop = fields.Date(u'截止日')
    date_settle = fields.Date(u'处理日')
    line_ids = fields.One2many('account.cash.dialy.flows.report.line', 'master_id', u'明细', required=False)



    def action_open_account_period_wizard(self):
        """
        打开 期别窗口
        :return:
        """
        self.ensure_one()
        self._cr.commit()
        context = {}
        context.update(active_model=self._name,
                       active_ids=self.ids,
                       active_id=self.id,
                       period_id=self.period_id.id
                       )
        self.env.context = context
        self._cr.execute("delete from account_cash_dialy_flows_wizard where create_uid=%s" % (self._uid,))
        wizard_id = self.env['account.cash.dialy.flows.wizard'].create(dict(active_id=self.id))
        return wizard_id.wizard_view()

    # 现金流量
    def init(self):
        self._cr.execute("""
            CREATE OR REPLACE FUNCTION account_cash_dialy_flows_report_settle(period_id_t integer,
                                                                              master_id_t integer,
                                                                              uid_t integer)
            RETURNS void AS
            $BODY$
                declare
                      begin_date_t timestamp;
                      end_date_t timestamp;
                      report_id_t Integer;
                      rec record;

                begin
                     delete from account_cash_dialy_flows_report_line;
                     select  to_date(to_char(date_start,'yyyy-01-01'),'yyyy-mm-dd'), date_stop into begin_date_t, end_date_t from account_period where id=period_id_t;
                     select id into report_id_t from account_report_format_designer where report_type='cash';

               --报表项目数据来源明细
                     create temp table account_dialy_flows_dd(
                         id integer,
                         master_id integer,
                         report_id integer,
                         sequence integer,
                         operator character varying,
                         operator_int integer,
                         amount_from character varying,
                         value_type character varying,
                         account_id integer,
                         report_line_id integer,
                         i1 numeric,
                         m1 numeric
                     );
            -- 报表格式明细
                     create temp table account_dialy_flows_line_t(
                        id integer,
                        name character varying,
                        master_id integer,
                        sequence integer,
                        print_sequence character varying,
                        llc integer,
                        partner_id integer,
                        percent_base_id integer,
                        i1 numeric,
                        m1 numeric
                     );

                     insert into account_dialy_flows_dd(id,master_id,report_id,sequence,operator,operator_int,amount_from,value_type,account_id,report_line_id)
                         select id,master_id,report_id,sequence,operator,operator_int,amount_from,value_type,account_id,report_line_id
                             from account_report_format_designer_line_dd
                         where report_id=report_id_t;

                     insert into account_dialy_flows_line_t (id,name,master_id,sequence,print_sequence,llc,partner_id,percent_base_id,i1,m1)
                         select id,name,master_id,sequence,print_sequence,llc,partner_id,percent_base_id,i1,m1
                             from account_report_format_designer_line
                             where master_id=report_id_t;

                -- 辅助核算
                    -- 支出和收款都是  【正数】
                     update account_dialy_flows_dd a set i1=coalesce(d.balance,0),
                                                                         m1=coalesce(d2.year_balance,0)
                                  from  account_dialy_flows_line_t b
                                       ,(select t1.sub_account_id,
                                                sum(coalesce(t1.debit,0))+sum(coalesce(t1.credit,0)) as balance
                                             from account_voucher_line t1
                                             left join account_voucher t2 on t1.master_id=t2.id
                                         where t2.period_id=period_id_t and t1.sub_account_id is not null
                                             group by t1.sub_account_id) d    -- 本期
                                       ,(select sub_account_id,
                                                sum(coalesce(t1.debit,0))+sum(coalesce(t1.credit,0)) as year_balance
                                            from account_voucher_line t1
                                            left join account_voucher t2 on t1.master_id=t2.id
                                         where t2.date between begin_date_t and end_date_t and t1.sub_account_id is not null
                                            group by t1.sub_account_id) d2    -- 年
                                 where a.master_id=b.id
                                   and a.account_id is null
                                   and a.amount_from ='subaccount' -- 该辅助核算
                                   and a.value_type ='debit_credit'
                                   and b.partner_id=d.sub_account_id
                                   and b.partner_id=d2.sub_account_id
                                   and b.partner_id is not null;
                -- 会计科目
                   -- 支出和收款都是  【正数】
                     update account_dialy_flows_dd a set i1=coalesce(d.balance,0),
                                                                                    m1=coalesce(d2.year_balance,0)
                                  from account_dialy_flows_line_t b
                                  ,(select t1.account_id,
                                             sum(coalesce(t1.debit,0))+sum(coalesce(t1.credit,0)) as balance
                                          from account_voucher_line t1
                                          left join account_voucher t2 on t1.master_id=t2.id
                                      where t2.period_id=period_id_t and t1.account_id is not null
                                      group by t1.account_id) d    -- 本期
                                  ,(select t1.account_id,
                                           sum(coalesce(t1.debit,0))+sum(coalesce(t1.credit,0)) as year_balance
                                          from account_voucher_line t1
                                          left join account_voucher t2 on t1.master_id=t2.id
                                      where t2.date between begin_date_t and end_date_t and t1.account_id is not null
                                      group by t1.account_id) d2    -- 年

                                 where a.master_id=b.id
                                   and a.account_id is not null
                                   and a.amount_from ='account' -- 会科
                                   and d.account_id=a.account_id
                                   and d2.account_id=a.account_id
                                   and a.value_type ='begin';

            --  本项目
                   for rec in select distinct coalesce(llc,0) as llc from account_dialy_flows_line_t order by coalesce(llc,0) desc
                    loop
                         update account_dialy_flows_line_t a set i1=coalesce(d.i1,0),
                                                                 m1=coalesce(d.m1,0)
                          from (select ee.master_id,sum(coalesce(ee.i1,0)) as i1
                                       ,sum(coalesce(ee.m1,0)) as m1
                                    from  account_dialy_flows_dd ee left join account_dialy_flows_line_t bb on ee.master_id=bb.id
                                       where bb.master_id=report_id_t
                                       group by ee.master_id
                                       ) d
                         where a.llc=rec.llc
                           and d.master_id=a.id;

                        update account_dialy_flows_dd a set i1=coalesce(d.i1,0),
                                                            m1=coalesce(d.m1,0)

                          from  account_dialy_flows_line_t b
                            ,(select t.* from account_dialy_flows_line_t t
                               where t.llc=rec.llc) d
                         where a.master_id=b.id
                           and a.report_line_id is not null
                           and a.amount_from ='report_line'
                           and b.llc=rec.llc-1
                           and a.report_line_id=d.id;

                           raise notice '%',rec.llc;

                    end loop;
        -- 根据报表设定来插入
                     insert into account_cash_dialy_flows_report_line(sub_account_id,name,print_sequence,sequence,master_id,period_id,balance,year_balance)
                         select b.partner_id as sub_account_id,
                                b.name,
                                b.print_sequence,b.sequence,
                                master_id_t as master_id,period_id_t as period_id,
                                b.i1 as balance,b.m1 as year_balance
                         from account_dialy_flows_line_t b
                         order by b.print_sequence; --现金流表

                     drop table account_dialy_flows_dd;
                     drop table account_dialy_flows_line_t;

                end;
                $BODY$
                    LANGUAGE plpgsql VOLATILE
                    COST 100;
        """)


class account_cash_dialy_flows_report_line(models.Model):
    _name = 'account.cash.dialy.flows.report.line'
    _description = u'日记账现金流表明细'
    _order = 'sequence'

    period_id = fields.Many2one('account.period', u'期别', ondelete="set null")
    sequence = fields.Integer(u'项次')
    name = fields.Char(u'项目')
    print_sequence = fields.Char(u'行次')
    balance = fields.Float(u'本月数', digits='Amount')
    year_balance = fields.Float(u'本年累计数', digits='Amount')
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', required=False,)
    master_id = fields.Many2one('account.cash.dialy.flows.report', u'主档', ondelete="cascade")
